#
# LuckyBot4, a python IRC bot
# (c) Copyright 2008 by Lucas van Dijk
# http://www.return1.net
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3, or (at your option)
# any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA
#
# $Id: __init__.py 70 2009-05-08 12:21:56Z luckyluke56 $
#

from __future__ import division
from luckybot import path
from luckybot.bot.plugins import Plugin
from luckybot.luckynet.protocols.irc import Format, ChannelMessage
import sqlite3 as sqlite
from datetime import datetime, timedelta
import string
import cPickle
import os

class StatsPlugin(Plugin):
	"""
		Main statistics plugin
	"""

	PLUGIN_INFO = {
		'name': 'Statistics',
		'description': 'Provides per channel user statistics',
		'authors': ['Lucas van Dijk'],
		'version': '1.0',
		'website': 'http://www.return1.net'
	}

	def initialize(self):
		self.register_command('top', self.on_top, help=self.lang.get('top_help'), args="[type]")
		self.register_command('stats', self.on_stats, help=self.lang.get('stats_help'), args="[user [type]]")
		self.register_command('seen', self.on_seen, help=self.lang.get('seen_help'), args="user")
		self.register_command('activity', self.on_activity, help=self.lang.get('activity_help'), args="[user]")
		self.register_command('setbot', self.on_setbot, help=self.lang.get('setbot_help'), args="nick [mode]")
		self.register_command('setnick', self.on_setnick, help=self.lang.get('setnick_help'), args="nick")
		self.register_command('merge', self.on_merge, help=self.lang.get('merge_help'), args="hostname userid")
		self.register_command('calculate_average', self.on_calculate_average, help=self.lang.get('calculate_avg_help'))
		self.register_message_handler(self.on_message)

		# Setup database connection
		self.connection = sqlite.connect(path.get_personal_file('stats.db'), detect_types=sqlite.PARSE_DECLTYPES | sqlite.PARSE_COLNAMES)
		self.connection.isolation_level = None
		self.connection.row_factory = sqlite.Row

		sql = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?"
		cursor = self.connection.execute(sql, ('servers',))
		data = cursor.fetchone()

		if data[0] == 0:
			sql = """
			CREATE TABLE IF NOT EXISTS servers (
				id integer NOT NULL PRIMARY KEY,
				server varchar(100)
			);"""

			self.connection.execute(sql)

			sql = """
			CREATE TABLE IF NOT EXISTS channels (
				id integer NOT NULL PRIMARY KEY,
				server_id integer NOT NULL,
				channel varchar(100)
			);"""

			self.connection.execute(sql)

			sql = """
			CREATE TABLE IF NOT EXISTS users (
				id integer NOT NULL PRIMARY KEY,
				channel_id integer NOT NULL,
				nick_id integer default 0,
				isbot integer(1) default 0
			);"""

			self.connection.execute(sql)

			sql = """
			CREATE TABLE IF NOT EXISTS hostnames (
				id integer NOT NULL PRIMARY KEY,
				user_id integer NOT NULL,
				hostname varchar(100),
				lines integer default 0,
				lines_morning integer default 0,
				lines_noon integer default 0,
				lines_evening integer default 0,
				lines_night integer default 0,
				words integer default 0,
				characters integer default 0,
				joins integer default 1,
				actions integer default 0
			);"""

			self.connection.execute(sql)

			sql = """
			CREATE TABLE IF NOT EXISTS nicks (
				id integer NOT NULL PRIMARY KEY,
				user_id integer NOT NULL,
				nickname varchar(32),
				isbot integer(1) default 0
			);"""

			self.connection.execute(sql)

			sql = """
			CREATE TABLE IF NOT EXISTS last_messages (
				id integer NOT NULL PRIMARY KEY,
				user_id integer NOT NULL UNIQUE,
				last_message varchar(255),
				message_date timestamp NOT NULL
			);
			"""

			self.connection.execute(sql)

			sql = """
			CREATE TABLE IF NOT EXISTS activity (
				channel_id integer NOT NULL,
				the_time timestamp NOT NULL,
				lines integer default 0
			);
			"""

			self.connection.execute(sql)

		if os.path.exists(path.get_personal_file('stats_data.p')):
			file = open(path.get_personal_file('stats_data.p'), 'rb')
			self.stats_data = cPickle.load(file)
			file.close()
		else:
			self.stats_data = None

		# Delete old activity
		sql = """
		DELETE FROM activity
		WHERE datetime(the_time, '+6 months') < datetime('now')
		"""

		self.connection.execute(sql)

	def on_top(self, event):
		"""
			Called when top command is used
		"""

		type = event.message.bot_args if event.message.bot_args else 'lines'

		if type == 'lines':
			# Get total number of lines
			sql = """
			SELECT SUM(h.lines) AS total
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
			total = cursor.fetchone()['total']

			sql = """
			SELECT SUM(h.lines) AS user_lines, n.nickname
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			GROUP BY h.user_id
			ORDER BY user_lines DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				print row

				item = {
					'value': row[0],
					'lang': self.lang.get('lines'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, total, items)

		elif type == 'words':
			sql = """
			SELECT SUM(h.words) AS total
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
			total = cursor.fetchone()['total']

			sql = """
			SELECT SUM(h.words) AS user_words, n.nickname
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			GROUP BY h.user_id
			ORDER BY user_words DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('words'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, total, items)
		elif type == 'chars':
			sql = """
			SELECT SUM(h.characters) AS total
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
			total = cursor.fetchone()['total']

			sql = """
			SELECT SUM(h.characters) AS user_chars, n.nickname
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			GROUP BY h.user_id
			ORDER BY user_chars DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('chars'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, total, items)
		elif type == 'spam':
			sql = """
			SELECT SUM(h.characters) / SUM(h.lines) AS spam, n.nickname
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			AND h.lines != 0
			GROUP BY h.user_id
			ORDER BY spam ASC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('spam'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, 0, items)
		elif type == 'me':
			sql = """
			SELECT SUM(h.actions) AS total
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
			total = cursor.fetchone()['total']

			sql = """
			SELECT SUM(h.actions) AS user_actions, n.nickname
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			GROUP BY h.user_id
			ORDER BY user_actions DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('actions'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, total, items)
		elif type == 'joins':
			sql = """
			SELECT SUM(h.joins) AS total
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
			total = cursor.fetchone()['total']

			sql = """
			SELECT SUM(h.joins) AS user_joins, n.nickname
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			AND h.joins != 0
			GROUP BY h.user_id
			ORDER BY user_joins DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('joins'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, total, items)
		elif type == 'l/j':
			sql = """
			SELECT SUM(h.lines) / SUM(h.joins) AS lines_per_join, n.nickname
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			JOIN nicks n
				ON u.nick_id = n.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			GROUP BY h.user_id
			ORDER BY lines_per_join DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('lines_per_join'),
					'nickname': row[1]
				}

				items.append(item)

			self.send_toplist(event, 0, items)
		elif type == 'nicks':
			sql = "SELECT id, nickname FROM nicks"
			cursor = self.connection.execute(sql)

			nicks = {}
			for row in cursor:
				nicks[row['id']] = row['nickname']

			sql = """
			SELECT COUNT(n.id) AS num_nicks, u.nick_id
			FROM nicks n
			JOIN users u
				ON n.user_id = u.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			AND (n.isbot == 0 AND u.isbot == 0)
			GROUP BY u.id
			ORDER BY num_nicks DESC
			LIMIT 10
			"""
			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))

			items = []
			for row in cursor:
				item = {
					'value': row[0],
					'lang': self.lang.get('num_nicks'),
					'nickname': nicks[row[1]]
				}

				items.append(item)

			self.send_toplist(event, 0, items)
		else:
			event.user.send_notice(self.lang.get('top_syntax').replace('!', self.bot.settings.get('Bot', 'command_prefix')))

	def send_toplist(self, event, total, items):
		"""
			Sends a toplist with given vars
		"""

		if not total:
			template = string.Template("[ ${c}#${num}${n} ]${c} ${nick}${n} - ${lang}:${c} ${value}${n}")
			template_short = string.Template("${c}[ #${num} ] ${nick}, ${value} ")
		else:
			template = string.Template("[ ${c}#${num}${n} ]${c} ${nick}${n} - ${lang}:${c} ${value}${n} (${percentage}%)")
			template_short = string.Template("${c}[ #${num} ] ${nick}, ${value} (${percentage}%) ")

		i = 1
		buffer = ""
		for item in items:
			vars = {
				'c': Format.color('darkblue'),
				'n': Format.normal(),
				'nick': item['nickname'],
				'num': i,
				'lang': item['lang'],
				'value': item['value'],
				'percentage': 0 if total == 0 else round((item['value'] * 100) / total, 1)
			}

			if i < 4:
				event.channel.send_pm(template.safe_substitute(vars))
			else:
				vars['c'] = Format.color('darkblue') if i % 2 == 0 else Format.normal()
				buffer += template_short.safe_substitute(vars)

			i += 1

		if buffer:
			event.channel.send_pm(buffer)

	def on_stats(self, event):
		"""
			Called when stats command is used
		"""

		args = event.message.bot_args.split()

		if not args:
			# get some totals, and calculate position of user
			sql = """
			SELECT h.*
			FROM hostnames h
			JOIN users u
				ON h.user_id = u.id
			JOIN channels c
				ON c.id = u.channel_id
			JOIN servers s
				ON c.server_id = s.id
			WHERE c.channel = ?
			AND s.server = ?
			GROUP BY u.id
			ORDER BY h.lines DESC
			"""

			cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
			total_lines = 0
			total_words = 0
			total_chars = 0
			total_joins = 0

			for row in cursor:
				total_lines += row['lines']
				total_words += row['words']
				total_chars += row['characters']
				total_joins += row['joins']

			template = string.Template('[${c}${b} ${channel}${n} ${l_stats} |${c} ${lines}${n} ${l_lines} |${c} ${words}${n} ${l_words} |${c} ${chars}${n} ${l_chars} |${c} ${wpl}${n} ${l_wpl} |${c} ${cpl}${n} ${l_cpl} ]')

			vars = {
				'lines': total_lines,
				'words': total_words,
				'chars': total_chars,
				'joins': total_joins,
				'channel': event.message.channel,
				'cpl': round(total_chars / total_lines, 1),
				'wpl': round(total_words / total_lines, 1),

				'l_lines': self.lang.get('lines'),
				'l_words': self.lang.get('words'),
				'l_chars': self.lang.get('chars'),
				'l_joins': self.lang.get('joins'),
				'l_stats': self.lang.get('stats'),
				'l_wpl': self.lang.get('words_per_line'),
				'l_cpl': self.lang.get('chars_per_line'),

				'c': Format.color('darkblue'),
				'b': Format.bold(),
				'n': Format.normal()
			}

			event.channel.send_pm(template.safe_substitute(vars))
		elif len(args) > 0:
			# Try if we can find a user
			sql = """
			SELECT h.id, h.user_id, n.nickname AS nickname, u.isbot AS isbot,
				SUM(h.lines) AS lines, SUM(h.joins) AS joins, SUM(h.words) AS words,
				SUM(h.characters) AS characters, SUM(h.actions) AS actions,
				n.isbot AS nick_isbot
			FROM nicks n
			JOIN hostnames h
				ON h.user_id = n.user_id
			JOIN users u
				ON u.id = n.user_id
			JOIN channels c
				ON u.channel_id = c.id
			JOIN servers s
				ON c.server_id = s.id
			WHERE s.server = ?
			AND c.channel = ?
			AND n.nickname = ?
			LIMIT 1
			"""

			cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel, args[0]))
			user = cursor.fetchone()

			if not user:
				event.channel.send_pm(self.lang.get('user_does_not_exists'))
				return

			type = args[1] if len(args) == 2 else 'default'

			if type == 'default':

				# get some totals, and calculate position of user
				sql = """
				SELECT h.*, u.isbot, n.isbot AS nick_isbot
				FROM hostnames h
				JOIN users u
					ON h.user_id = u.id
				JOIN nicks n
					ON n.user_id = u.id
				JOIN channels c
					ON c.id = u.channel_id
				JOIN servers s
					ON c.server_id = s.id
				WHERE c.channel = ?
				AND s.server = ?
				GROUP BY u.id
				ORDER BY h.lines DESC
				"""

				cursor = self.connection.execute(sql, (event.message.channel, event.client.connection.addr[0]))
				total_lines = 0
				total_words = 0
				total_chars = 0
				total_joins = 0

				pos = -1
				i = 1
				for row in cursor:
					print i, "-", row
					total_lines += row['lines']
					total_words += row['words']
					total_chars += row['characters']
					total_joins += row['joins']

					if row['id'] == user[0]:
						pos = i

					i += 1

				data = {
					'nick': user['nickname'],
					'bot': '[Bot] ' if user['isbot'] or user['nick_isbot'] else '',
					'lines': user['lines'],
					'lines_pct': round((user['lines'] * 100) / total_lines, 1),
					'words': user['words'],
					'words_pct': round((user['words'] * 100) / total_words, 1),
					'chars': user['characters'],
					'chars_pct': round((user['characters'] * 100) / total_chars, 1),
					'joins': user['joins'],
					'joins_pct': round((user['joins'] * 100) / total_joins, 1),
					'lpj': round(user['lines'] / user['joins']),
					'cpl': round(user['characters'] / user['lines']),
					'wpl': round(user['words'] / user['lines']),
					'pos': pos,

					'b': Format.bold(),
					'c': Format.color('darkblue'),
					'n': Format.normal(),

					'l_lines': self.lang.get('lines'),
					'l_words': self.lang.get('words'),
					'l_chars': self.lang.get('chars'),
					'l_joins': self.lang.get('joins'),
					'l_lpj': self.lang.get('lines_per_join'),
					'l_cpl': self.lang.get('chars_per_line'),
					'l_wpl': self.lang.get('words_per_line')
				}

				template1 = string.Template('[ ${bot}${c}${b}${nick}${b}${n} (${c}#${pos}${n}) ${l_lines}:${c} ${lines}${n} (${lines_pct}%) | ${l_joins}:${c} ${joins}${n} (${joins_pct}%),${c} ${lpj}${n} ${l_lpj} ]')
				template2 = string.Template('${l_chars}:${c} ${chars}${n} (${chars_pct}%),${c} ${cpl}${n} ${l_cpl} | ${l_words}:${c} ${words}${n} (${words_pct}%),${c} ${wpl}${n} ${l_wpl}')

				event.channel.send_pm(template1.safe_substitute(data))
				event.channel.send_pm(template2.safe_substitute(data))
			elif type == 'spam':
				template = string.Template('[${c}${b} ${nick}${n} ] ${l_cpl}:${c} ${cpl}')
				data = {
					'nick': user['nickname'],
					'cpl': round(user['characters'] / user['lines'], 1),

					'l_cpl': self.lang.get('chars_per_line'),

					'b': Format.bold(),
					'c': Format.color('darkblue'),
					'n': Format.normal(),
				}

				event.channel.send_pm(template.safe_substitute(data))
			elif type == 'me':
				template = string.Template('[${c}${b} ${nick}${n} ] ${l_actions}:${c} ${actions}')
				data = {
					'nick': user['nickname'],
					'actions': user['actions'],

					'l_actions': self.lang.get('actions'),

					'b': Format.bold(),
					'c': Format.color('darkblue'),
					'n': Format.normal(),
				}

				event.channel.send_pm(template.safe_substitute(data))
			elif type == 'nicks':
				sql = """
				SELECT nickname
				FROM nicks
				WHERE user_id = ?
				"""

				cursor = self.connection.execute(sql, (user[1],))

				to_send = []
				buffer = ""
				num_nicks = 0
				for row in cursor:
					buffer += row['nickname'] + ", "
					num_nicks += 1

					if len(buffer) > 255:
						to_send.append(buffer[0:-2])
						buffer = ""

				if buffer:
					to_send.append(buffer[0:-2])

				vars = {
					'nickname': user['nickname'],
					'num_nicks': str(num_nicks)
				}

				event.channel.send_pm(self.lang.get('nicknames_of', vars))
				for line in to_send:
					event.channel.send_pm(line)
			else:
				event.user.send_notice(self.lang.get('stats_syntax').replace('!', self.bot.settings.get('Bot', 'command_prefix')))

	def on_seen(self, event):
		"""
			Called when seen command is used
		"""

		if not event.message.bot_args:
			event.user.send_notice(self.lang.get('seen_syntax').replace('!', self.bot.settings.get('Bot', 'command_prefix')))
			return

		# Try if we can find a user
		sql = """
		SELECT n.nickname, m.*
		FROM nicks n
		JOIN users u
			ON u.id = n.user_id
		JOIN last_messages m
			ON m.user_id = u.id
		JOIN channels c
			ON u.channel_id = c.id
		JOIN servers s
			ON c.server_id = s.id
		WHERE s.server = ?
		AND c.channel = ?
		AND n.nickname = ?
		LIMIT 1
		"""

		cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel, event.message.bot_args))
		user = cursor.fetchone()

		if not user:
			event.channel.send_pm(self.lang.get('user_does_not_exists'))
			return

		template = string.Template('${n}[ ${l_last_message}${c} ${nick}${n}: ${last_message} ${l_on}${c} ${time} ]')

		vars = {
			'nick': user['nickname'],
			'last_message': user['last_message'],
			'time': user['message_date'].strftime('%d-%m-%Y %H:%M:%S'),

			'l_last_message': self.lang.get('last_message'),
			'l_on': self.lang.get('on'),

			'c': Format.color('darkblue'),
			'n': Format.normal()
		}

		event.channel.send_pm(template.safe_substitute(vars))

	def on_activity(self, event):
		"""
			Called when activity command is used
		"""

		if event.message.bot_args:
			# Try if we can find a user
			sql = """
			SELECT h.id AS id, SUM(h.lines) AS lines, SUM(h.joins) AS joins, SUM(h.words) AS words, SUM(h.characters) AS characters, SUM(h.actions) AS actions,
				SUM(h.lines_morning) AS lines_morning, SUM(h.lines_noon) AS lines_noon, SUM(h.lines_evening) AS lines_evening, SUM(h.lines_night) AS lines_night,
				n.nickname AS nickname
			FROM nicks n
			JOIN hostnames h
				ON h.user_id = n.user_id
			JOIN users u
				ON u.id = n.user_id
			JOIN channels c
				ON u.channel_id = c.id
			JOIN servers s
				ON c.server_id = s.id
			WHERE s.server = ?
			AND c.channel = ?
			AND n.nickname = ?
			LIMIT 1
			"""

			cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel, event.message.bot_args))
			user = cursor.fetchone()

			if not user:
				event.channel.send_pm(self.lang.get('user_does_not_exists'))
				return

			template = string.Template('${c}${b}${l_activity_of}${n} ${nick}:${c} ${l_morning}:${n} ${morning} (${morning_pct}%) |${c} ${l_noon}:${n} ${noon} (${noon_pct}%) |${c} ${l_evening}:${n} ${evening} (${evening_pct}%) |${c} ${l_night}: ${night} (${night_pct}%)')
			vars = {
				'b': Format.bold(),
				'c': Format.color('darkblue'),
				'n': Format.normal(),

				'nick': user['nickname'],
				'morning': user['lines_morning'],
				'morning_pct': round((user['lines_morning'] / user['lines']) * 100, 1),
				'noon': user['lines_noon'],
				'noon_pct': round((user['lines_noon'] / user['lines']) * 100, 1),
				'evening': user['lines_evening'],
				'evening_pct': round((user['lines_evening'] / user['lines']) * 100, 1),
				'night': user['lines_night'],
				'night_pct': round((user['lines_night'] / user['lines']) * 100, 1),

				'l_activity_of': self.lang.get('activity_of'),
				'l_morning': self.lang.get('morning'),
				'l_noon': self.lang.get('noon'),
				'l_evening': self.lang.get('evening'),
				'l_night': self.lang.get('night')
			}

			event.channel.send_pm(template.safe_substitute(vars))
		else:
			sql = """
			SELECT a.*
			FROM activity a
			JOIN channels c
				ON a.channel_id = c.id
			JOIN servers s
				ON c.server_id = s.id
			WHERE s.server = ?
			AND c.channel = ?
			AND strftime('%d', a.the_time) = ?
			AND strftime('%m', a.the_time) = ?
			AND strftime('%Y', a.the_time) = ?
			"""

			params = (event.client.connection.addr[0], event.message.channel,
				datetime.now().strftime('%d'), datetime.now().strftime('%m'), datetime.now().strftime('%Y')
			)
			cursor = self.connection.execute(sql, params)

			lines = {}
			total = 0
			channel_id = -1
			for row in cursor:
				if channel_id == -1:
					channel_id = row['channel_id']

				lines[row['the_time'].hour] = row['lines']
				total += row['lines']

			num_hours = datetime.now().hour + (datetime.now().minute / 60)

			average = round(total / num_hours, 1)

			# Calculate activity compared with average
			averages = self.stats_data['averages'][channel_id] if channel_id in self.stats_data['averages'] else {}
			activity = []
			for hour in lines:
				activity.append(lines[hour] / (averages[hour] if hour in averages else average))

			average_activity = sum(activity) / len(activity)

			# Calculate expected lines for today
			expected_lines = 0
			old = 0
			for i in range(24):
				if i in lines:
					if i == datetime.now().hour:
						# current hour, which is not finished yet
						expected_lines += lines[i] + (((averages[i] if i in averages else average) * (datetime.now().minute / 60)) * average_activity)
					else:
						expected_lines += lines[i]
				else:
					expected_lines += (averages[i] if i in averages else average) * average_activity

				difference = abs(old - expected_lines)
				old = expected_lines

			# Average number of lines
			average_lines = round(sum(averages.itervalues()))

			# Get most active day
			sql = """
			SELECT a.the_time, SUM(lines) AS total_lines, strftime('%d-%m-%Y', a.the_time) AS the_date
			FROM activity a
			JOIN channels c
				ON a.channel_id = c.id
			JOIN servers s
				ON c.server_id = s.id
			WHERE s.server = ?
			AND c.channel = ?
			GROUP BY the_date
			ORDER BY total_lines DESC
			"""

			cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel))
			data = cursor.fetchone()

			template1 = string.Template('${c}${b}${l_most_active_day}:${n} ${date} ${l_with}${c} ${most_lines}${n} ${l_lines} | ${l_avg_lines}:${c} ${avg_lines}')
			template2 = string.Template('${c}${b}${l_activity_today}:${n} ${lines} ${l_lines} (${l_avg_ph}:${c} ${avg}${n}) | ${l_expected}:${c} ${expected}${n} ${l_lines}')

			vars = {
				'c': Format.color('darkblue'),
				'b': Format.bold(),
				'n': Format.normal(),

				'date': data[2],
				'most_lines': data[1],
				'lines': total,
				'expected': int(expected_lines),
				'avg': average,
				'avg_lines': average_lines,

				'l_most_active_day': self.lang.get('most_active_day'),
				'l_lines': self.lang.get('lines'),
				'l_with': self.lang.get('with'),
				'l_activity_today': self.lang.get('activity_today'),
				'l_avg_ph': self.lang.get('avg_ph'),
				'l_expected': self.lang.get('expected'),
				'l_avg_lines': self.lang.get('avg_lines')
			}

			event.channel.send_pm(template1.safe_substitute(vars))
			event.channel.send_pm(template2.safe_substitute(vars))

	def on_setbot(self, event):
		"""
			Called when setbot command is used, sets a nick or user as
			a bot
		"""

		if not event.user.check_logged_in():
			event.user.send_notice(self.lang.get('access_denied'))
			return

		args = event.message.bot_args.split()
		if not args:
			event.user.send_notice(self.lang.get('setbot_syntax').replace('!', self.bot.settings.get('Bot', 'command_prefix')))
			return

		# Try if we can find a user
		sql = """
		SELECT u.id
		FROM nicks n
		JOIN users u
			ON u.id = n.user_id
		JOIN channels c
			ON u.channel_id = c.id
		JOIN servers s
			ON c.server_id = s.id
		WHERE s.server = ?
		AND c.channel = ?
		AND n.nickname = ?
		LIMIT 1
		"""

		cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel, args[0]))
		user = cursor.fetchone()

		if not user:
			event.channel.send_pm(self.lang.get('user_does_not_exists'))
			return

		mode = args[1] if len(args) > 1 else 'true'

		if mode == 'true':
			sql = "UPDATE users SET isbot=1 WHERE id = ?"
			self.connection.execute(sql, (user['id'],))
		elif mode == 'nickonly':
			sql = "UPDATE nicks SET isbot=1 WHERE nickname = ? AND user_id = ?"
			self.connection.execute(sql, (args[0], user['id']))
		elif mode == 'false':
			sql = "UPDATE users SET isbot=0 WHERE id = ?"
			self.connection.execute(sql, (user['id'],))

			sql = "UPDATE nicks SET isbot=0 WHERE nickname = ? and user_id = ?"
			self.connection.execute(sql, (args[0], user['id']))
		else:
			event.user.send_notice(self.lang.get('setbot_syntax').replace('!', self.bot.settings.get('Bot', 'command_prefix')))
			return

		message = self.lang.get('user_marked_as_bot') if mode in ['true', 'nickonly'] else self.lang.get('user_not_marked_as_bot')
		event.channel.send_pm(message)

	def on_setnick(self, event):
		"""
			Called on setnick command, sets stats nickname
		"""

		if not event.message.bot_args:
			event.user.send_notice(self.lang.get('setnick_syntax').replace('!', self.bot.settings.get('Bot', 'command_prefix')))
			return

		# Try if we can find a user
		sql = """
		SELECT u.id
		FROM nicks n
		JOIN users u
			ON u.id = n.user_id
		JOIN channels c
			ON u.channel_id = c.id
		JOIN servers s
			ON c.server_id = s.id
		WHERE s.server = ?
		AND c.channel = ?
		AND n.nickname = ?
		LIMIT 1
		"""

		cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel, event.user.nick))
		user = cursor.fetchone()

		# Check if the given nick belongs to the current user
		sql = """
		SELECT n.id, n.nickname
		FROM nicks n
		JOIN users u
			ON u.id = n.user_id
		JOIN channels c
			ON u.channel_id = c.id
		JOIN servers s
			ON s.id = c.server_id
		WHERE s.server = ?
		AND c.channel = ?
		AND n.nickname = ?
		AND u.id = ?
		"""

		cursor = self.connection.execute(sql, (event.client.connection.addr[0], event.message.channel, event.message.bot_args, user['id']))
		data = cursor.fetchone()

		if not data:
			event.channel.send_pm(self.lang.get('nick_not_owned'))
			return

		# Update new stats nick
		sql = "UPDATE users SET nick_id = ? WHERE id = ?"
		self.connection.execute(sql, (data['id'], user['id']))

		event.channel.send_pm(self.lang.get('nick_set') % data['nickname'])

	def on_merge(self, event):
		"""
			Command used to merge 2 hostnames
		"""

		args = event.message.bot_args.split()

	def on_message(self, event):
		"""
			Called on each message
		"""

		if isinstance(event.message, ChannelMessage):
			if event.user.nick.lower() in ['chanserv', 'nickserv', 'memoserv', event.client.nickname.lower()]:
				return

			channel_name = event.message.message if event.message.command == 'JOIN' else event.message.channel

			if not channel_name.startswith('#'):
				return

			# Get Server data
			sql = "SELECT * FROM servers WHERE server = ? LIMIT 1"
			cursor = self.connection.execute(sql, (event.client.connection.addr[0],))

			server = cursor.fetchone()

			if not server:
				# Insert into database
				sql = "INSERT INTO servers (server) VALUES (?)"
				cursor = self.connection.execute(sql, (event.client.connection.addr[0],))

				server = {
					'id': cursor.lastrowid,
					'server': event.client.connection.addr[0]
				}

			# Get channel data
			sql = "SELECT * FROM channels WHERE server_id = ? AND channel = ? LIMIT 1"
			cursor = self.connection.execute(sql, (server['id'], channel_name))

			channel = cursor.fetchone()

			if not channel:
				# Insert into database
				sql = "INSERT INTO channels (server_id, channel) VALUES (?, ?)"
				cursor = self.connection.execute(sql, (server['id'], channel_name))

				channel = {
					'id': cursor.lastrowid,
					'server_id': server['id'],
					'channel': channel_name
				}

			# Check if this user (hostname) exists in database
			sql = """
			SELECT h.id, h.user_id, u.isbot, u.nick_id
			FROM hostnames h
			JOIN users u
				ON u.id = h.user_id
			WHERE channel_id = ?
			AND h.hostname = ?
			LIMIT 1
			"""

			cursor = self.connection.execute(sql, (channel['id'], event.user.hostname))
			user = cursor.fetchone()

			if not user:
				# Hostname does not exists
				# Insert new hostname and user entry in database
				sql = "INSERT INTO users (channel_id) VALUES (?)"
				cursor = self.connection.execute(sql, (channel['id'],))
				user_id = cursor.lastrowid

				sql = "INSERT INTO hostnames (user_id, hostname) VALUES (?, ?)"
				self.connection.execute(sql, (user_id, event.user.hostname))

				user = {
					'id': cursor.lastrowid,
					'user_id': user_id,
					'isbot': 0,
					'nick_id': 0
				}

			isbot = user['isbot'] == 1

			# Check if nickname exists in database
			sql = "SELECT id, nickname, isbot FROM nicks WHERE user_id = ? AND nickname = ? LIMIT 1"
			cursor = self.connection.execute(sql, (user['user_id'], event.user.nick))

			nickdata = cursor.fetchone()
			if not nickdata:
				sql = "INSERT INTO nicks (user_id, nickname) VALUES (?, ?)"
				cursor = self.connection.execute(sql, (user['user_id'], event.user.nick))

				if not user['nick_id']:
					nick_id = cursor.lastrowid
					sql = "UPDATE users SET nick_id = ? WHERE id = ?"
					self.connection.execute(sql, (nick_id, user['id']))

			else:
				isbot = bool(nickdata['isbot'])

				if not user['nick_id']:
					sql = "UPDATE users SET nick_id = ? WHERE id = ?"
					self.connection.execute(sql, (nickdata['id'], user['user_id']))

			if event.message.command == 'JOIN':
				# Someone's joining, update number of joins
				sql = "UPDATE hostnames SET joins = joins + 1 WHERE id = ?"
				self.connection.execute(sql, (user['id'],))
			else:
				# Well, update the user statistics, if the user is not marked as bot
				if not isbot:
					hour = datetime.now().hour

					day_part = ''
					if hour >= 0 and hour < 6:
						day_part = 'night'
					elif hour >= 6 and hour < 12:
						day_part = 'morning'
					elif hour >= 12 and hour < 18:
						day_part = 'noon'
					else:
						day_part = 'evening'

					word_count = event.message.message.count(' ') + 1
					character_count = len(event.message.message)

					sql_actions = ''
					if event.message.message.startswith("\001ACTION"):
						sql_actions = ", actions = actions + 1"

					sql = """
					UPDATE hostnames SET
						lines = lines + 1,
						lines_%s = lines_%s + 1,
						words = words + ?,
						characters = characters + ?
						%s
					WHERE id = ?
					""" % (day_part, day_part, sql_actions)

					self.connection.execute(sql, (word_count, character_count, user['id']))

				# Update last message
				sql = """
				INSERT OR REPLACE INTO last_messages (user_id, last_message, message_date)
				VALUES (?, ?, ?)
				"""

				self.connection.execute(sql, (user['user_id'], event.message.message, datetime.now()))

				# Update activity
				sql = """
				UPDATE activity SET
					lines = lines + 1
				WHERE the_time = datetime(?)
				AND channel_id = ?
				"""

				cursor = self.connection.execute(sql, (datetime.now().strftime('%Y-%m-%d %H:00'), channel['id']))

				if cursor.rowcount == 0:
					# No rows affected, insert it
					sql = "INSERT INTO activity (channel_id, the_time, lines) VALUES(?, datetime(?), ?)"
					self.connection.execute(sql, (channel['id'], datetime.now().strftime('%Y-%m-%d %H:00'), 1))

				if self.stats_data == None or self.stats_data['last_check'] + timedelta(days=2) < datetime.now():
					# Calculate average lines per hour for each channel
					self.calculate_statsdata()

	def on_calculate_average(self, event):
		"""
			Called when calculate average command is used
		"""

		if not event.user.check_logged_in():
			event.user.send_notice(self.lang.get('access_denied'))
			return

		self.calculate_statsdata()
		event.channel.send_pm(self.lang.get('done'))

	def calculate_statsdata(self):
		"""
			Calculates all stats data
		"""

		self.stats_data = {
			'last_check': datetime.now(),
			'averages': self.calculate_average()
		}

		print self.stats_data

		file = open(path.get_personal_file('stats_data.p'), 'wb')
		cPickle.dump(self.stats_data, file, -1)
		file.close()

	def calculate_average(self):
		"""
			Calculates the average lines per hour for each channel
			and returns a list with each channel and the averages per
			hour
		"""

		channel_averages = {}

		for hour in range(24):
			sql = """
			SELECT SUM(lines) / COUNT(lines) AS average, channel_id
			FROM activity
			WHERE strftime('%H', the_time) == ?
			GROUP BY channel_id
			"""

			cursor = self.connection.execute(sql, ('%02d' % hour,))

			for channel in cursor:
				if not channel['channel_id'] in channel_averages:
					channel_averages[channel['channel_id']] = {}

				channel_averages[channel['channel_id']][hour] = channel['average']

		return channel_averages

	def destroy(self):
		self.connection.close()
